package njust.edu.dao.impl;

import njust.edu.dao.UserDao;
import njust.edu.entity.Dish;
import njust.edu.entity.User;


import java.sql.*;
import njust.edu.dbutils.SimpleUtils;
import njust.edu.web.Page;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl  implements UserDao {



    public User findById(Long id) {
        String sql = "select id,name,password,type,tell from user where id = ?";
        User user = new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            while(rs.next()){
                user  = new User(rs.getLong(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getInt(4),
                        rs.getString(5)
                        );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return user;


    }

    @Override
    public User findByName(String name) {
        String sql = "select id,name,password,type,tell from user where name = ?";
        User user =new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while(rs.next()){
                user  = new User(rs.getLong(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getInt(4),
                        rs.getString(5)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return user;
    }

    @Override
    public void update(User user) {
        String sql = "update user set name=?,password=?,type=?,tell=? where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getType());
            ps.setString(4, user.getTell());
            ps.setLong(5, user.getId());
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public void delete(Long id) {
        String sql = "delete from user where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public void add(User user) {
        String sql = "insert into user(id,name,password,type,tell) "
                + "values(?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1,user.getId());
            ps.setString(2, user.getName());
            ps.setString(3, user.getPassword());
            ps.setInt(4, user.getType());
            ps.setString(5, user.getTell());
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
    }

    @Override
    public List<User> getList() {
        String sql = "SELECT id,name,password,type,tell " + "FROM user";
        List<User> lUser = new ArrayList<>();
        User user = new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                user  = new User(rs.getLong(1)
                        ,rs.getString(2)
                        ,rs.getString(3)
                        ,rs.getInt(4)
                        ,rs.getString(5)
                       );
                lUser.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return lUser;
    }

    @Override
    public long getTotalUserNumber() {
        String sql = "SELECT count(id) FROM user where type=3";
        long count =-1;
        Connection conn =null;
        PreparedStatement ps= null;
        ResultSet rs = null;

        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        SimpleUtils.release(conn,ps,rs);
        return count;
    }

    @Override
    public List<User> getPageList(int n, int pageSize) {
        String sql = "SELECT id,name,password,type,tell "
                + "FROM user  LIMIT ?, ? ";
        Connection conn =null;
        Statement stmt= null;
        PreparedStatement ps= null;
        ResultSet rs = null;
        List<User> users=new ArrayList<>();

        try {
            conn = SimpleUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (n-1)*pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while(rs.next()){
                User user=new User(rs.getLong("id"),rs.getString("name"),rs.getString("password"),rs.getInt("type"),rs.getString("tell"));
                //System.out.println(user.getId());
                users.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        SimpleUtils.release(conn,ps,rs);
        return users;

    }

    @Override
    public Page<User> getPage(int n) {

        Page<User> page = new Page<User>(n);
        //System.out.println(getTotalUserNumber());
        page.setTotalItemNumber(getTotalUserNumber());
        // 校验 pageNo 的合法性
        n = page.getPageNo();
        page.setList(getPageList(n, 10));
        return page;
    }


}
